/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.model;

import com.javabeans.Cargo;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.db.SqlManager;
import com.exception.DataException;


public class AdminCargo implements CargoManager{

    Connection c;
    public AdminCargo(Connection c){
        this.c = c;
    }

    @Override
    public int getIdCargo() throws DataException {
        int res = 0;
        ResultSet rs = null;
        PreparedStatement preSt = null;
        String query = "SELECT IFNULL(max(IDCARGO)+1,1) AS ID FROM CARGO";
        try{
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            rs.next();
            res = rs.getInt("ID");
        }catch(SQLException e){
            throw new DataException(" AdminCargo -> getIdCargo() "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return res;
    }

    @Override
    public void saveCargo(Cargo crg) throws DataException {
        
        PreparedStatement preSt = null;
        String query = "";
        
        try{
            query="INSERT INTO CARGO VALUES("+crg.getIdCargo();
            query+= ", '"+crg.getNombre().toUpperCase();
            query+= "',"+crg.getActivo()+")";            
           preSt = c.prepareStatement(query);
            preSt.executeUpdate();
        }catch(SQLException ex){
           throw new DataException("Error saveCargo(Cargo ca) "+ex.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
    }

    @Override
    public Cargo getCargo(int idCargo) throws DataException {
         Cargo crg = new Cargo();
        ResultSet rs = null;
        PreparedStatement preSt = null;
        try{
            String query = "SELECT IDCARGO, NOMBRE,ACTIVO";
            query += " FROM CARGO";
            query += " WHERE IDCARGO = "+idCargo;            
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            while(rs.next()){
                crg.setIdCargo(rs.getInt("IDPAIS"));
                crg.setNombre(rs.getString("NOMBRE"));                
                crg.setActivo(rs.getBoolean("ACTIVO"));
            }
        }catch(SQLException e){
            throw new DataException(" AdminCargo -> getCargo(int idCargo) e = "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return crg;
    }

    @Override
    public List<Cargo> getCargos() throws DataException {
        List<Cargo> cargos = new ArrayList<Cargo>();
        ResultSet rs = null;
        PreparedStatement preSt = null;
        try{
            String query = "SELECT IDCARGO, NOMBRE, ACTIVO";
            query += " FROM CARGO";
            query += " WHERE ACTIVO = 1";
            query += " ORDER BY NOMBRE";            
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            while(rs.next()){
                Cargo c = new Cargo();
                c.setIdCargo(rs.getInt("IDCARGO"));
                c.setNombre(rs.getString("NOMBRE"));
                c.setActivo(rs.getBoolean("ACTIVO"));
                
                cargos.add(c);
            }
        }catch(SQLException e){
            throw new DataException(" AdminCargo -> getCargos() e = "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return cargos;
    }

    @Override
    public void updateCargo(Cargo crg) throws DataException {
        PreparedStatement preSt = null;
        try{
            String query = "UPDATE CARGO SET NOMBRE = '"+crg.getNombre().toUpperCase();
            query += "' WHERE IDCARGO = "+crg.getIdCargo();            
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
        }catch(SQLException ex){
            throw new DataException(" AdminCargo -> updateCargo(Cargo crg) e = "+ex.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
    }
    

    @Override
    public void deleteCargo(int idCargo) throws DataException {
        
        PreparedStatement preSt = null;
        String query = "DELETE FROM CARGO WHERE IDCARGO="+idCargo;
        try{
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
            // ahora eliminamos sus dependencia
            query = "DELETE FROM DETALLE_ADMINISTRATIVO WHERE IDCARGO="+idCargo;
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
            
        }catch(SQLException e){
            throw new DataException("Error! en AdminCargo -> deleteCargo(idCargo) e = "+e.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
    }

    @Override
    public List<Cargo> getCargosBaja() throws DataException {
        List<Cargo> cargos = new ArrayList<Cargo>();
        ResultSet rs = null;
        PreparedStatement preSt = null;
        try{
            String query = "SELECT IDCARGO, NOMBRE, ACTIVO";
            query += " FROM CARGO";
            query += " WHERE ACTIVO = 0";
            query += " ORDER BY NOMBRE";            
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            while(rs.next()){
                Cargo carg = new Cargo();
                carg.setIdCargo(rs.getInt("IDCARGO"));
                carg.setNombre(rs.getString("NOMBRE"));
                carg.setActivo(rs.getBoolean("ACTIVO"));
                
                cargos.add(carg);
            }
        }catch(SQLException e){
            throw new DataException(" AdminCargo -> getCargosBaja() e = "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return cargos;
    }
    
    @Override
    public List<Cargo> getCargosDadosDeBaja()throws DataException{
    
        List<Cargo> cargos = new ArrayList<Cargo>();
        ResultSet rs = null;
        PreparedStatement preSt = null;
        try{
            String query = "SELECT IDCARGO, NOMBRE, ACTIVO";
            query += " FROM CARGO";
            query += " WHERE ACTIVO = 0";
            query += " ORDER BY NOMBRE";            
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            while(rs.next()){
                Cargo carg = new Cargo();
                carg.setIdCargo(rs.getInt("IDCARGO"));
                carg.setNombre(rs.getString("NOMBRE"));
                carg.setActivo(rs.getBoolean("ACTIVO"));
                cargos.add(carg);
            }
        }catch(SQLException e){
            throw new DataException(" AdminCargo -> getCargosDadosDeBaja() e = "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return cargos;
    }
    
    @Override
    public boolean isUseInDetalleAdminist(int idCargo) throws DataException{
    
     boolean res = false;
        ResultSet rs = null;
        PreparedStatement preSt = null;
        String query = "SELECT IDDETALLE_ADMINISTRATIVO";
        query += " FROM DETALLE_ADMINISTRATIVO";
        query += " WHERE IDCARGO = "+idCargo;
        query += " LIMIT 1";
        try{
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            if(rs.next())
                res = true;
        }catch(SQLException e){
            throw new DataException(" AdminCargo -> isUseInDetalleAdminist() "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return res;
    
    }
    
     @Override
     public void darBaja(int idCargo) throws DataException{
        PreparedStatement preSt = null;
        try{
            String query = "UPDATE CARGO SET ACTIVO = 0 WHERE IDCARGO="+idCargo;
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();           
        }catch(SQLException ex){
            throw new DataException(" AdminCargo -> darBaja(int idCargo) e = "+ex.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
    }
     
    @Override
    public void darAlta(int idCargo) throws DataException{
        PreparedStatement preSt = null;
        try{
            String query = "UPDATE CARGO SET ACTIVO = 1 WHERE IDCARGO="+idCargo;
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();           
        }catch(SQLException ex){
            throw new DataException(" AdminCargo -> darAlta(int idCargo) e = "+ex.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
    }   
    
}
